﻿using Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class ProductService
    {

        // 商品
        //新增商品
        public SqlDataReader SelProduct(string id)
        {
            string sql = string.Format("select *  from product where id='{0}'", id);
            return DBhelper.DataReader(sql);
        }

        public bool AddProduct(Product product) 
        {
            /// <summary>
            /// 新增商品
            /// </summary>
            string sql = string.Format("insert Product values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')",
                product.Proname,
                product.Protype,
                product.Proylcd,
                product.Place,
                product.Burd, 
                product.Speci,
                product.Expir, 
                product.Norm,
                product.Perms,
                product.Way,
                product.Thumbnail,
                product.Pic,
                product.Price,
                product.Remark,
                product.Stroe);
            return DBhelper.ex(sql);
        }
        public SqlDataReader Getproduct(string name)
        {
            string sql = string.Format("select top 5 ProName from product where ProType=(select id from productType where TypeName='{0}')", name);
            return DBhelper.DataReader(sql);
        }
        public SqlDataReader Getproducts(string name)
        {
            string sql = string.Format("select top 8 * from product where ProType=(select id from productType where TypeName='{0}')", name);
            return DBhelper.DataReader(sql);
        }
        public SqlDataReader Getproducts3(string name)
        {
            string sql =string.Format("select  * from Product where proname like '%{0}%'", name);
            return DBhelper.DataReader(sql);
        }

        public SqlDataReader Getproducts2(string name, int stroe, int protype, string proylcd)
        {
            string sql = "select  * from Product where proname like '%"+name+"%' and 1=1 ";
            if (stroe != 0)
            {
                sql += " and store='"+stroe+"'";

            }
            else if (protype != 0)
            {
                sql += " and protype='"+protype+" '";


            }
            else if (proylcd != "" && proylcd != null)
            {
                sql += " and proylcd='"+proylcd+ "' ";

            }
            
            return DBhelper.DataReader(sql);
        }
        //查询产地
        public SqlDataReader Getproduct5()
        {
            string sql = string.Format("select Proylcd  from product  group by Proylcd");
            return DBhelper.DataReader(sql);
        }
        //查询商品的规格
        public SqlDataReader SelCartSpeci( string proid)
        {                   //int    int  int   string  string  stting s    int     s     int     s                              
            //购物车内显示出购物车ID 数量 商品id 商品名 商品图片 规格 单价 店家id 店家 买家id 买家账号
            string sql = string.Format("select Speci from  product where id='{0}'", proid);
            return DBhelper.DataReader(sql);
        }
        //查询对应店铺的商品
        public SqlDataReader SelProductByStoreId(string storeid) 
        {
            string sql =string.Format("select top 3 p.id, ProName,Price,thumbnail from product p join store s on p.Store=s.Id  where store='{0}'", storeid);
            return DBhelper.DataReader(sql);
        }
        //通过商品类型id查询对应的商品
        public SqlDataReader SelProducByProType(string ProType) 
        {
            string sql = "select*from product where ProType ='" + ProType + "'";
            return DBhelper.DataReader(sql);

        }
    }
}
